package com.threeti.jiayusms.domain.service.legacy;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Service;

import com.threeti.jiayusms.domain.model.legacy.Condition;
import com.threeti.jiayusms.domain.model.legacy.ConditionType;
import com.threeti.jiayusms.domain.model.legacy.Customer;
import com.threeti.jiayusms.domain.model.legacy.Department;
import com.threeti.jiayusms.domain.model.legacy.Node;
import com.threeti.jiayusms.domain.model.legacy.Order;
import com.threeti.jiayusms.domain.model.legacy.PreviewOrder;
import com.threeti.jiayusms.domain.model.legacy.ProductType;
import com.threeti.jiayusms.domain.model.legacy.SendAddress;
import com.threeti.jiayusms.framework.util.PagedListHandler;
import com.threeti.jiayusms.infrastructure.legacy.JiaYuDBLegacyDao;

@Service
public class JiaYuDBLegacyQueryServiceImpl implements JiaYuDBLegacyQueryService {
  @Autowired SimpleJdbcTemplate template;

  @Autowired  
  private JiaYuDBLegacyDao dao;

  @Override
  public Order findOrderBy(String orderid) {
	String sql = "select * from dorder where orderid = ?";
	return template.queryForObject(sql, new OrderRowMapper(), orderid);
  }

  @Override
	public PagedListHandler<Order> findAllOrderByPage(int currentPageIndex, int startingIndex) {
		return null;
	}

	@Override
  public Department findDepartmentBy(String deptcode) {
	String sql = "select * from bdepartment where deptcode = ?";

	String deptCity = "";
	String sql2 = "select deptcode from tx_DeptRelate where reldeptcode = ?";
	try {
	  deptCity = template.queryForObject(sql2, String.class, deptcode);
	} catch (DataAccessException e) {
	  e.printStackTrace();
	}

	return template.queryForObject(sql, new DepartmentRowMapper(deptCity), deptcode);
  }

  @Override
  public Customer findCustomerBy(String clientid) {
	String sql = "select * from dclient where clientid = ?";
	return template.queryForObject(sql, new CustomerRowMapper(), clientid);
  }

  /*@Override
  @Deprecated
  public SendAddress findSendAddressBy(String orderid) {
	String sql = "select * from v_sendaddress where orderid = ?";
	return template.queryForObject(sql, new SendAddressRowMapper(), orderid);
  }*/

  @Override
  public SendAddress findSendAddressBy2(String orderid){
	  String sql = "select * from txv_GetCargoAddress where orderid = ?";
	  return template.queryForObject(sql, new SendAddressRowMapper2(), orderid);
  }

  @Override
  public List<Node> findAllNodes() {
	String sql = "select * from tx_NodeInfo";
	return template.query(sql, new NodeRowMapper());
  }  

  @Override
  public List<ProductType> findAllProductTypes() {
	String sql = "select * from tx_ProductInfo";
	return template.query(sql, new ProductTypeRowMapper());
  }

  @Override
  public List<ConditionType> findAllConditionTypes() {
	String sql = "select * from tx_ConditionInfo";
	return template.query(sql, new ConditionTypeRowMapper());
  }

  @Override
  public List<Condition> findAllConditions() {
	String sql = "select * from tx_ConditionValueInfo";
	return template.query(sql, new ConditionRowMapper());
  }

  @Override
  public PagedListHandler<PreviewOrder> findAllPreviewOrdersByPage(int currentPageIndex, int startingIndex) {
	return dao.findAllPreviewOrdersByPage(currentPageIndex, startingIndex);
  }

  private class OrderRowMapper implements RowMapper<Order> {
	public Order mapRow(ResultSet rs, int rownum) throws SQLException {
	  Order order = new Order();
	  order.setOrderid(rs.getString("orderid"));
	  order.setCargono(rs.getString("cargono"));
	  order.setCargoname(rs.getString("cargoname"));
	  order.setCargocount(rs.getLong("cargocount"));
	  order.setClientid(rs.getString("clientid"));
	  order.setShipper(rs.getString("shipper"));
	  order.setShipperphone(rs.getString("shipperphone"));
	  order.setShipperMP(rs.getString("shipperMP"));
	  order.setSignaturer(rs.getString("signaturer"));
	  order.setSignaturephone(rs.getString("signaturephone"));
	  order.setSignatureMP(rs.getString("signatureMP"));
	  order.setTotalfee(rs.getLong("totalfee"));
	  order.setSendfee(rs.getLong("sendfee"));
	  order.setArrivefee(rs.getLong("arrivefee"));
	  order.setMonthfee(rs.getLong("monthfee"));
	  order.setGatherfee(rs.getLong("gatherfee"));
	  order.setLoaddeptid(rs.getString("loaddeptid"));
	  order.setUnloadeptid(rs.getString("unloadeptid"));
	  order.setSendcargotype3(rs.getString("sendcargotype3"));

	  return order;
	}
  }

  private class DepartmentRowMapper implements RowMapper<Department> {
	private String deptCity;

	public DepartmentRowMapper(String deptCity) {
	  this.deptCity = deptCity;
	}

	public Department mapRow(ResultSet rs, int rownum) throws SQLException {
	  Department department = new Department();
	  department.setDeptcode(rs.getString("deptcode"));
	  department.setDeptname(rs.getString("deptname"));
	  department.setSimplename(rs.getString("simplename"));
	  department.setDeptadd(rs.getString("deptadd"));
	  department.setSendphone(rs.getString("sendphone"));
	  department.setArrivequeryphone(rs.getString("arrivequeryphone"));
	  department.setServicephone(rs.getString("servicephone"));
	  department.setRelatedCity(deptCity);//rs.getString("city")不能使用这个city，该city代表了其它含义

	  return department;
	}
  }

  private class CustomerRowMapper implements RowMapper<Customer> {
	public Customer mapRow(ResultSet rs, int rownum) throws SQLException {
	  Customer customer = new Customer();
	  customer.setClientid(rs.getString("clientid"));
	  customer.setClientname(rs.getString("clientname"));
	  customer.setSimpleclientname(rs.getString("simpleclientname"));
	  customer.setSPphone(rs.getString("SPphone"));

	  return customer;
	}
  }

  private class SendAddressRowMapper implements RowMapper<SendAddress> {
	public SendAddress mapRow(ResultSet rs, int rownum) throws SQLException {
	  return new SendAddress(rs.getString("orderid"), rs.getString("sendaddress"));
	}
  }

  private class SendAddressRowMapper2 implements RowMapper<SendAddress> {
	public SendAddress mapRow(ResultSet rs, int rownum) throws SQLException {
	  return new SendAddress(rs.getString("orderid"), rs.getString("GetCargoAddress"));
	}
  }

  private class NodeRowMapper implements RowMapper<Node> {
	public Node mapRow(ResultSet rs, int rownum) throws SQLException {
	  Node node = new Node();
	  node.setNid(rs.getInt("nid"));
	  node.setNcode(rs.getString("ncode"));
	  node.setNname(rs.getString("nname"));
	  return node;
	}
  }

  private class ProductTypeRowMapper implements RowMapper<ProductType> {
	public ProductType mapRow(ResultSet rs, int rownum) throws SQLException {
	  ProductType pType = new ProductType();
	  pType.setPid(rs.getInt("pid"));
	  pType.setPcode(rs.getString("pcode"));
	  pType.setPname(rs.getString("pname"));
	  return pType;
	}
  }

  private class ConditionTypeRowMapper implements RowMapper<ConditionType> {
	public ConditionType mapRow(ResultSet rs, int rownum) throws SQLException {
	  ConditionType cType = new ConditionType();
	  cType.setCid(rs.getInt("cid"));
	  cType.setCcode(rs.getString("ccode"));
	  cType.setCname(rs.getString("cname"));
	  return cType;
	}
  }

  private class ConditionRowMapper implements RowMapper<Condition> {
	public Condition mapRow(ResultSet rs, int rownum) throws SQLException {
	  Condition c = new Condition();
	  c.setCvid(rs.getInt("cvid"));
	  c.setCvcode(rs.getString("cvcode"));
	  c.setCvname(rs.getString("cvname"));
	  return c;
	}
  }

}
